﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Globalization;
using System.Data.SqlClient;
using System.Runtime.InteropServices;

using CommandLine;//CLP

//SSIS
using Microsoft.SqlServer.Dts.Runtime; //ControlFlow - Managed
using Microsoft.SqlServer.Dts.Pipeline; //DataFlow - Managed
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; //Dataflow - Interop


namespace BA.SqlServer.Ssis.Dfl

{
    public class CallDiscoverer
    {
        static void Main(string[] args)
        {
            DateTime startTime = DateTime.Now;
            int Count;
            try
            {
                
                //get commandline parameters
                CLPArguments parsedArgs = new CLPArguments();
                if (!CommandLine.Parser.ParseArgumentsWithUsage(args, parsedArgs))
                    return;

                Console.WriteLine(DateTime.Now.ToLongTimeString() + " INFO: DFL Discovery Started...");

                if (parsedArgs.IsArgsValid())
                {
                    DFLDiscoverer Discoverer = new DFLDiscoverer(parsedArgs);
                    Count = Discoverer.Discover();

                    if (Count == -1)
                        return;

                    DateTime endTime = DateTime.Now;

                    Console.WriteLine(DateTime.Now.ToLongTimeString() + " INFO: DFL Discovery Complete. ");
                    Console.WriteLine("No. of Dataflows in the package = " + Discoverer.DFLCount.ToString(CultureInfo.CurrentCulture));
                    Console.WriteLine("No. of metadata records written = " + Count.ToString(CultureInfo.CurrentCulture));
                    Console.WriteLine("Total Time Taken = " + endTime.Subtract(startTime).TotalSeconds.ToString(CultureInfo.CurrentCulture) + " seconds");
                }
            }
            catch (ArgumentException argException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + argException.Message);
                return;
            }
            catch (StackOverflowException stackOverflowException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + stackOverflowException.Message);
                return;
            }
            catch (SqlException sqlException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + sqlException.Message);
                return;
            }
            catch (NullReferenceException nullRefException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + nullRefException.Message);
                return;
            }
            catch (InvalidOperationException invalidOpException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + invalidOpException.Message);
                return;
            }
            catch (DtsRuntimeException ssisRuntimeException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + ssisRuntimeException.Message);
                return;
            }
            catch (DtsPipelineException ssisDataflowException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + ssisDataflowException.Message);
                return;
            }
            catch (DtsTaskException ssisTaskException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + ssisTaskException.Message);
                return;
            }
            catch (DtsComponentException ssisComponentException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + ssisComponentException.Message);
                return;
            }
            catch (DtsComException ssisComException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + ssisComException.Message);
                return;
            }
            catch (DtsException ssisException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + ssisException.Message);
                return;
            }
            catch (COMException COMException)
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString() + " " + COMException.Message);
                return;
            }
 
       }


    }

    public class CLPArguments
    {
        [Argument(ArgumentType.Required, HelpText = "Fully qualified path of SSIS Package in File System. E.g C:\\packages\\myPackage.dtsx")]
        public string package;

        [Argument(ArgumentType.Required, HelpText = "Database Server Name hosting the DFLMetadata table. If Instance Name is applicable, then specify as DBServerName/InstanceName. E.g localhost")]
        public string server;

        [Argument(ArgumentType.Required, HelpText = "Database Name hosting the DFLMetadata table. E.g ETLMetadata")]
        public string database;

        [Argument(ArgumentType.AtMostOnce, HelpText = "Database User Name for SQL authentication. This parameter is not required for Windows authentication.")]
        public string user;

        [Argument(ArgumentType.AtMostOnce, HelpText = "Password of Database User required for SQL authentication. This parameter is not required for Windows authentication.")]
        public string pwd;

        //Method to validate commandline arguments
        /// <summary>
        /// Validate Commandline Arguments
        /// </summary>
        /// <exception cref="ArgumentException"></exception>
        /// <returns>True on Success</returns>
        public Boolean IsArgsValid()
        {
            bool retVal = false;
            //validate whether package exists
            if (!File.Exists(this.package))
                throw new ArgumentException("ERROR: The package " + this.package + " does not exist or this application has no access to the package");

            if (Path.GetExtension(this.package).ToLower() != ".dtsx")
                throw new ArgumentException("ERROR: " + this.package + " is an invalid package");

            //validate sql server connection string
            if (this.user != null && this.pwd == null)
                throw new ArgumentException("ERROR: Password not supplied for SQL Server username " + this.user);

            if (this.user == null && this.pwd != null)
                throw new ArgumentException("ERROR: SQL Server username not supplied.");

            String ConnectionString;
            if (this.user != null && this.pwd != null) //sql authentication
                ConnectionString = "Data Source=" + this.server + ";Initial Catalog=" + this.database + ";User ID=" + this.user + ";Password=" + this.pwd + ";";
            else //windows authentication
                ConnectionString = "Data Source=" + this.server + ";Initial Catalog=" + this.database + ";Integrated Security=True;";

            DFLMetaDataContext dflDB = new DFLMetaDataContext(ConnectionString);
            retVal = dflDB.DatabaseExists();
            if (!retVal)
                throw new ArgumentException("ERROR: Connection string parameters of DFLMetadata table is invalid.");

            return retVal;
        }
    }
}


